﻿using Dapper;
using liaowa2.Entity.DataModel;
using liaowa2.Entity.DTO;
using liaowa2.Entity.DTO.ManagerRole;
using liaowa2.Entity.Web;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace liaowa2.Dao.Manager
{
    public class ManagerRoleDao
    {
        public static bool Add(ManagerRole model, List<ManagerRoleValue> list)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSqlRole = new StringBuilder();

            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                var trans = conn.BeginTransaction();

                strSql.Append("insert into ManagerRole(");
                strSql.Append("role_name,role_type,is_sys");
                strSql.Append(") values (");
                strSql.Append("@role_name,@role_type,@is_sys");
                strSql.Append(") ");
                strSql.Append(";select @@IDENTITY");

                var p = new DynamicParameters();
                p.Add("@role_name", model.role_name);
                p.Add("@role_type", model.role_type);
                p.Add("@is_sys", model.is_sys);

                var r = conn.Query<int>(strSql.ToString(), p, trans, commandType: CommandType.Text).FirstOrDefault();


                if (r > 0)
                {
                    try
                    {
                        for (int i = 0; i < list.Count; i++)
                        {
                            strSqlRole.Append("insert into ManagerRoleValue(");
                            strSqlRole.Append("role_id,nav_name,action_type");
                            strSqlRole.Append(") values (");
                            strSqlRole.Append("@role_id,@nav_name,@action_type");
                            strSqlRole.Append("); ");

                            var result = conn.Execute(strSqlRole.ToString(), new
                            {
                                role_id = r,
                                nav_name = list[i].nav_name,
                                action_type = list[i].action_type
                            }, trans, commandType: CommandType.Text);
                        }
                    }
                    catch (Exception)
                    {
                        trans.Rollback();
                        return false;
                    }
                }
                else
                {
                    trans.Rollback();
                    return false;
                }
                trans.Commit();
                conn.Close();
            }

            return true;

        }

        public static bool Update(ManagerRole model, List<ManagerRoleValue> list)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSqlRole = new StringBuilder();

            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                var trans = conn.BeginTransaction();

                strSql.Append("update ManagerRole set ");
                strSql.Append(" role_name = @role_name , ");
                strSql.Append(" role_type = @role_type , ");
                strSql.Append(" is_sys = @is_sys  ");
                strSql.Append(" where id = @id ");

                var r = conn.Execute(strSql.ToString(), new
                {
                    id = model.id,
                    role_name = model.role_name,
                    role_type = model.role_type,
                    is_sys = model.is_sys
                }, trans, commandType: CommandType.Text);


                if (r > 0)
                {
                    try
                    {
                        conn.Execute("delete ManagerRoleValue where role_id=@id", new { role_id = r }, commandType: CommandType.Text);

                        for (int i = 0; i < list.Count; i++)
                        {
                            strSqlRole.Append("insert into ManagerRoleValue(");
                            strSqlRole.Append("role_id,nav_name,action_type");
                            strSqlRole.Append(") values (");
                            strSqlRole.Append("@role_id,@nav_name,@action_type");
                            strSqlRole.Append("); ");

                            var result = conn.Execute(strSqlRole.ToString(), new
                            {
                                role_id = model.id,
                                nav_name = list[i].nav_name,
                                action_type = list[i].action_type
                            }, trans, commandType: CommandType.Text);
                        }
                    }
                    catch (Exception)
                    {
                        trans.Rollback();
                        return false;
                    }
                }
                else
                {
                    trans.Rollback();
                    return false;
                }
                trans.Commit();
                conn.Close();
            }

            return true;

        }

        public static ManagerRoleDTO GetRoleDTO(int id)
        {
            var p = new DynamicParameters();
            p.Add("@id", id);

            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select id, role_name, role_type, is_sys  ");
                strSql.Append("  from ManagerRole ");
                strSql.Append(" where id = @id ");

                var result = conn.Query<ManagerRoleDTO>(strSql.ToString(), p, commandType: CommandType.Text).FirstOrDefault();
                if (result != null)
                {
                    result.manager_role_values = ManagerRoleValueDao.GetList(" role_id=" + result.id);
                }
                else
                {
                    return null;
                }
                return result;
            }
        }

        public static EntityPage<ManagerRoleDto> GetAll(GetAllInput input)
        {
            string whereStr = " 1 = 1 ";
            var p = new DynamicParameters();
            if (!string.IsNullOrEmpty(input.RoleName))
            {
                whereStr += " and name like %@Name% ";
            }
            p.Add("@Count", dbType: DbType.Int32, direction: ParameterDirection.Output);
            p.Add("@tab", "ManagerRole");
            p.Add("@strFld", "*");
            p.Add("@strWhere", whereStr);
            p.Add("@PageIndex", input.CurrentIndex);
            p.Add("@PageSize", input.PageSize);
            p.Add("@Sort", "Id desc");

            EntityPage<ManagerRoleDto> result = new EntityPage<ManagerRoleDto>();
            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                result.Items = conn.Query<ManagerRoleDto>("Common_PageList", p, commandType: CommandType.StoredProcedure).ToList();
            }
            result.Count = p.Get<int>("@Count");
            return result;
        }

        #region 生成代码

        public static bool Exists(string where)
        {
            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select count(1) from ManagerRole where ");
                strSql.Append(where);

                var model = conn.Execute(strSql.ToString(), null, commandType: CommandType.Text);

                return model > 0 ? true : false;
            }
        }



        /// <summary>
        /// 增加一条数据,返回ID
        /// </summary>
        public static int Add(ManagerRole model)
        {
            DepperSqlHelp<ManagerRole> help = new DepperSqlHelp<ManagerRole>();
            int result = help.InsertReturn(model, "ManagerRole");
            return result;
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static bool Update(ManagerRole model)
        {
            DepperSqlHelp<ManagerRole> help = new DepperSqlHelp<ManagerRole>();
            bool result = help.Update(model, "ManagerRole", " where id=" + model.id);
            return result;
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public static bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ManagerRole ");
            strSql.Append(" where id = @id ");

            var p = new DynamicParameters();
            p.Add("@id", id);

            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                var result = conn.Query<int>(strSql.ToString(), p, commandType: CommandType.Text).FirstOrDefault();
                if (result > 0)
                {
                    return true;
                }
                return true;
            }
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public static bool DeleteList(string idlist)
        {
            var ids = idlist.Split(',');
            int result = 0;

            var p = new DynamicParameters();
            p.Add("@IntID", idlist);

            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                string sql = " delete ManagerRole where id in (" + string.Join(",", ids) + "); ";
                result = conn.Query<int>(sql, p, commandType: CommandType.Text).FirstOrDefault();
            }
            if (result > 0)
            {
                return true;
            }
            return true;
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public static ManagerRole GetModel(int id)
        {
            var p = new DynamicParameters();
            p.Add("@Id", id);

            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * ");
                strSql.Append(" FROM ManagerRole");
                strSql.Append(" where id=@Id");

                var model = conn.Query<ManagerRole>(strSql.ToString(), p, commandType: CommandType.Text).FirstOrDefault();
                return model;
            }
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static List<ManagerRole> GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM ManagerRole ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }

            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                var model = conn.Query<ManagerRole>(strSql.ToString(), null, commandType: CommandType.Text).ToList();
                return model;
            }
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public static List<ManagerRole> GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM ManagerRole ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);

            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                var model = conn.Query<ManagerRole>(strSql.ToString(), null, commandType: CommandType.Text).ToList();
                return model;
            }
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public static int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM ManagerRole ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                var model = conn.Query<int>(strSql.ToString(), null, commandType: CommandType.Text).FirstOrDefault();
                return model;
            }
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public static List<ManagerRole> GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.Rid desc");
            }
            strSql.Append(")AS Row, T.*  from ManagerRole T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            using (IDbConnection conn = SqlConn.OpenConnectionStar())
            {
                var model = conn.Query<ManagerRole>(strSql.ToString(), null, commandType: CommandType.Text).ToList();
                return model;
            }
        }

        #endregion
    }
}
